'''
电眼看四川项目
获取同花顺中各企业的财报数据
2020年12月24日
zys
'''
import requests
from bs4 import BeautifulSoup
import lxml, xlrd, xlwt, time
# 下载文件依赖包
import os,re,json,urllib3
from urllib import request
urllib3.disable_warnings()
# 从xlutils中导入copy这个功能
import xlutils
from xlutils import copy

count = 0

# 显示下载进度
def Schedule(a,b,c):
    '''''
    a:已经下载的数据块
    b:数据块的大小
    c:远程文件的大小
   '''
    if c!=0:
        per = 100.0 * a * b / c
        if per > 100 :
            per = 100
        # print('%.2f%%' % per)
    else:
        print('文件下载失败!')

# 文件下载
def downHandle(url, fileName):
    # url = 'http://basic.10jqka.com.cn/api/stock/export.php?export=main&type=simple&code=300366'
    myheaders = [('User - Agent', 'Mozilla/5.0 (Windows; U; Windows NT 5.2) AppleWebKit/525.13'
                                  ' (KHTML, like Gecko) Version/3.1 Safari/525.13'),]
    opener = request.build_opener()
    opener.addheaders = myheaders
    request.install_opener(opener)
    local = os.path.join('downFile',fileName)
    request.urlretrieve(url, local, Schedule)

# 获取Excel企业数据并写入新的文档
def getExcelQyData(fileName, new_sheet):
    global count
    # 获取文件对象
    workbook0 = xlrd.open_workbook('D:\\WordCode\\python\\tongHuaShunHandle\\downFile\\'+fileName)
    # 查看文件中所有的工作表名
    sNameS0 = workbook0.sheet_names()[0]
    # sheet索引从0开始
    sheet0 = workbook0.sheet_by_index(0)
    # sheet的名称，行数，列数
    rowN0 = sheet0.nrows
    colN0 = sheet0.ncols
    print(count)
    for rowV0 in range(1, rowN0):
        new_sheet.write(rowV0+count, 0, fileName)
        for colV0 in range(0,colN0):
            cellV0 = sheet0.cell_value(rowV0,colV0)
            new_sheet.write(rowV0+count, colV0+1, cellV0)
        count =  count+1

# --------------传入url,爬取数据，写入excel-------------- #
def main():
    # 获取文件对象
    workbook = xlrd.open_workbook('ssmd.xls')

    # 然后用xlutils里面的copy功能，复制一个excel
    # new_book = copy.copy(workbook)
    new_book = xlwt.Workbook()
    new_sheet = new_book.add_sheet("结果数据",cell_overwrite_ok=True)
    '''
    new_sheet.write(0, 0, '证券代码')
    new_sheet.write(0, 1, '公司名称')
    new_sheet.write(0, 2, '日期')
    new_sheet.write(0, 3, '开盘价格')
    new_sheet.write(0, 4, '高位价格')
    new_sheet.write(0, 5, '低位价格')
    new_sheet.write(0, 6, '收盘价格')
    new_sheet.write(0, 7, '成交量')
    new_sheet.write(0, 8, '成交额')
    new_sheet.write(0, 9, '换手率')
    '''
    # 查看文件中所有的工作表名
    sNameS = workbook.sheet_names()[0]
    # sheet索引从0开始
    sheet = workbook.sheet_by_index(0)
    # sheet的名称，行数，列数
    rowN = sheet.nrows
    colN = sheet.ncols
    # print(sheet.name, rowN, colN)

    # 循环Sheet获取行列数据
    for rowV in range(2, rowN):
        cV = sheet.cell_value(rowV, 0)
        cVJC = sheet.cell_value(rowV, 1)
        cVName = sheet.cell_value(rowV, 2)
        if cV != '':
            url = 'http://basic.10jqka.com.cn/api/stock/export.php?export=main&type=simple&code='+cV
            print(cV, '-->', cVJC, '-->', cVName, '>>', url)
            fileName = cVName+'_'+cV+'.xls'
            # 下载文件
            downHandle(url, fileName)
            # 获取文件写入Excel
            getExcelQyData(fileName, new_sheet)
            print('[',cVName+'_'+cV+'.xls',']文件下载成功!')
    # 保存新工作簿
    new_book.save('D:\\WordCode\\python\\tongHuaShunHandle\\downFile\\ssmdResult.xls')
    print("-----数据采集完成-----")

# 执行入口
if __name__ == '__main__':
    main()
